<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH w_NAVIGATION AS
  (SELECT T.*,
    row_number() over(order by T_NAVIGATION.ORDER_NUM, T_NAVIGATION.code, T.ORDER_NUM , t.CODE) rn
    , T_NAVIGATION.code as parentCode
    , T_NAVIGATION.name as parentName
  FROM C1_NAVIGATION T
  LEFT JOIN C1_NAVIGATION T_NAVIGATION
  ON ((NOW() BETWEEN IFNULL(T_NAVIGATION.BEGIN_DATE, NOW()) AND IFNULL(T_NAVIGATION.END_DATE, NOW()))
  AND T_NAVIGATION.id = T.parent_id)
  WHERE (NOW() BETWEEN IFNULL(T.BEGIN_DATE, NOW()) AND IFNULL(T.END_DATE, NOW()))
<#if StringUtils.isNotBlank(siteId)>
  AND T.site_id                              = :siteId
</#if>
<#if StringUtils.isNotBlank(typeId)>
  AND T.type_id                              = :typeId
</#if>
<#if StringUtils.isNotBlank(parentCode)>
  AND (instr(T_NAVIGATION.code, :parentCode) > 0
  OR instr(T_NAVIGATION.name, :parentCode)   > 0)
</#if>
<#if StringUtils.isNotBlank(code)>
  AND (instr(T.code, :code) > 0
  OR instr(T.name, :code)   > 0)
</#if>
  )
SELECT T.*
    , t_site.code as siteCode
    , t_site.name as siteName
    , t_DICTIONARY.code as typeCode
    , t_DICTIONARY.name as typeName
FROM w_NAVIGATION T
left join c1_site t_site on (t_site.id = t.site_id)
left join C1_DICTIONARY t_DICTIONARY on (t_DICTIONARY.id = t.type_id)
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>
